﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using KPIS.GERP.GIMS.MODEL;
using System.Data;
using KPIS.DBM;
using System.Configuration;

namespace KPIS.GERP.GIMS.DAL
{
    public class D_IcmInventoryControl
    {
        DBManager dbManager = new DBManager(DataProvider.MySQL, ConfigurationSettings.AppSettings["ConnectionString"].ToString());
        ConvertNullable cn = new ConvertNullable();

        public IList<M_IcmInventoryControl> GetList()
        {
            string strSQL;
            DataRowCollection drc = null;
            IList<M_IcmInventoryControl> infoList = new List<M_IcmInventoryControl>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@DeleteRecord", SystemInfo._DeleteRecordStatus);

                strSQL = " SELECT rc.icm_receive_seq,icm_receive_code,icm_dn_seq,rc.icm_res_type_seq,ri.icm_res_item_seq "
                        +" ,quantity,rc.price,quantity_remain_before,quantity_remain_after,quantity_remain "
                        +" ,receive_date,rc.remark,rc.record_status "
                        +" , concat(COALESCE(icm_res_item_code,''),' ',COALESCE(resource.icm_res_item_name,'')) as res_item_name  "
                        + ",rt.icm_res_type_name,js.job_site_name,wh.icm_wh_name,assloc.to_other "
                        +" ,case when (js.job_site_name is not null) then js.job_site_name when (wh.icm_wh_name is not null) then wh.icm_wh_name else assloc.to_other end location_name "
                        + " ,rc.receive_type_seq, rct.receive_type_name,js.job_site_seq,wh.icm_wh_seq,un.icm_unit_name "
                        + " FROM icm_receive rc "
                        + " LEFT JOIN icm_mas_resource_type rt on rc.icm_res_type_seq = rt.icm_res_type_seq"
                        //+" LEFT JOIN icm_mas_object_type ojt ON ojt.icm_obj_type_seq = rc.icm_obj_type_seq "
                        +" LEFT JOIN icm_receive_item ri on rc.icm_receive_seq = ri.icm_receive_seq"
                        +" LEFT JOIN icm_mas_resource_item resource ON ri.icm_res_item_seq = resource.icm_res_item_seq "
                        +" LEFT JOIN icm_asset ass ON rc.icm_receive_seq = ass.icm_receive_seq "
                        +" LEFT JOIN icm_asset_location assloc ON (ass.asset_code = assloc.asset_code  and location_end_date is null) "
                        +" LEFT JOIN hrm_mp_job_sites js ON assloc.to_job_site_seq = js.job_site_seq "
                        +" LEFT JOIN icm_mas_warehouse wh ON assloc.to_wh_seq = wh.icm_wh_seq "
                        +" LEFT JOIN icm_mas_receive_type rct ON rc.receive_type_seq = rct.receive_type_seq " 
                        +" LEFT JOIN icm_mas_unit un ON resource.icm_unit_seq = un.icm_unit_seq "
                        + " where rc.record_status <> @DeleteRecord";

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    for (int i = 0; i < drc.Count; i++)
                    {
                        M_IcmInventoryControl info = new M_IcmInventoryControl();
                        info.icm_receive_seq = Convert.ToInt32(drc[i]["icm_receive_seq"]);
                        info.icm_receive_code = drc[i]["icm_receive_code"].ToString();
                        info.icm_dn_seq = cn.ConvertNullInt(drc[i]["icm_dn_seq"]);
                        info.icm_obj_type_seq = cn.ConvertNullInt(drc[i]["icm_res_type_seq"]);
                        info.icm_res_item_seq = cn.ConvertNullInt(drc[i]["icm_res_item_seq"]);
                        info.quantity = cn.ConvertNullDouble(drc[i]["quantity"]);
                        info.price = cn.ConvertNullDouble(drc[i]["price"]);
                        info.quantity_remain_before = cn.ConvertNullDouble(drc[i]["quantity_remain_before"]);
                        info.quantity_remain_after = cn.ConvertNullDouble(drc[i]["quantity_remain_after"]);
                        info.quantity_remain = cn.ConvertNullDouble(drc[i]["quantity_remain"]);
                        info.receive_date = cn.ConvertNullDateTime(drc[i]["receive_date"]);
                        info.remark = drc[i]["remark"].ToString();
                        info.res_item_name = drc[i]["res_item_name"].ToString();
                        info.obj_type_name = drc[i]["icm_res_type_name"].ToString();
                        info.location_name = drc[i]["location_name"].ToString();
                        info.receive_type_name = drc[i]["receive_type_name"].ToString();
                        info.unit_name = drc[i]["icm_unit_name"].ToString();
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public M_IcmInventoryControl GetListFillBySeq(Nullable<Int32> icm_receive_seq)
        {
            string strSQL;
            DataRowCollection drc = null;
            M_IcmInventoryControl info = new M_IcmInventoryControl();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@icm_receive_seq", icm_receive_seq);

                strSQL = " SELECT rc.icm_receive_seq,icm_receive_code,icm_dn_seq,ojt.icm_obj_type_seq,rc.icm_res_item_seq "
                        + " ,quantity,rc.price,quantity_remain_before,quantity_remain_after,quantity_remain "
                        + " ,receive_date,rc.remark,rc.record_status "
                        + " , concat(COALESCE(icm_res_item_code,''),' ',COALESCE(resource.icm_res_item_name,'')) as res_item_name,ojt.icm_obj_type_name  "
                        + " ,js.job_site_name,wh.icm_wh_name,assloc.to_other "
                        + " ,case when (js.job_site_name is not null) then js.job_site_name when (wh.icm_wh_name is not null) then wh.icm_wh_name else assloc.to_other end location_name "
                        + " ,rc.receive_type_seq, rct.receive_type_name,js.job_site_seq,wh.icm_wh_seq "
                        + " FROM icm_receive rc "
                        + " LEFT JOIN icm_mas_object_type ojt ON ojt.icm_obj_type_seq = rc.icm_obj_type_seq "
                        + " LEFT JOIN icm_mas_resource_item resource ON rc.icm_res_item_seq = resource.icm_res_item_seq "
                        + " LEFT JOIN icm_asset ass ON rc.icm_receive_seq = ass.icm_receive_seq "
                        + " LEFT JOIN icm_asset_location assloc ON (ass.asset_code = assloc.asset_code  and location_end_date is null) "
                        + " LEFT JOIN hrm_mp_job_sites js ON assloc.to_job_site_seq = js.job_site_seq "
                        + " LEFT JOIN icm_mas_warehouse wh ON assloc.to_wh_seq = wh.icm_wh_seq "
                        + " LEFT JOIN icm_mas_receive_type rct ON rc.receive_type_seq = rct.receive_type_seq "
                        + " LEFT JOIN icm_mas_unit un ON resource.icm_unit_seq = un.icm_unit_seq "
                        + " WHERE rc.icm_receive_seq = @icm_receive_seq ";

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    info.icm_receive_seq = Convert.ToInt32(drc[0]["icm_receive_seq"]);
                    info.icm_receive_code = drc[0]["icm_receive_code"].ToString();
                    info.icm_dn_seq = cn.ConvertNullInt(drc[0]["icm_dn_seq"]);
                    info.icm_obj_type_seq = cn.ConvertNullInt(drc[0]["icm_obj_type_seq"]);
                    info.icm_res_item_seq = cn.ConvertNullInt(drc[0]["icm_res_item_seq"]);
                    info.quantity = cn.ConvertNullDouble(drc[0]["quantity"]);
                    info.price = cn.ConvertNullDouble(drc[0]["price"]);
                    info.quantity_remain_before = cn.ConvertNullDouble(drc[0]["quantity_remain_before"]);
                    info.quantity_remain_after = cn.ConvertNullDouble(drc[0]["quantity_remain_after"]);
                    info.quantity_remain = cn.ConvertNullDouble(drc[0]["quantity_remain"]);
                    info.receive_date = cn.ConvertNullDateTime(drc[0]["receive_date"]);
                    info.remark = drc[0]["remark"].ToString();
                    info.res_item_name = drc[0]["res_item_name"].ToString();
                    info.obj_type_name = drc[0]["icm_obj_type_name"].ToString();
                    info.location_name = drc[0]["location_name"].ToString();
                    info.unit_name = drc[0]["icm_unit_name"].ToString();
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return info;
        }
    }
}
